package com.example.managemyself.data.repository;

import android.util.Log;
import androidx.lifecycle.LiveData;
import androidx.lifecycle.MutableLiveData;

import com.example.managemyself.data.model.Task;
import com.example.managemyself.util.MySQLConnectionHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

/**
 * 任务远程MySQL仓库实现
 */
public class MySQLTaskRepository implements ITaskRepository {
    private static final String TAG = "MySQLTaskRepository";
    
    private final ExecutorService executorService;
    private final MySQLConnectionHelper dbHelper;
    
    // 缓存的LiveData对象
    private final MutableLiveData<List<Task>> allTasksLiveData = new MutableLiveData<>();
    private final MutableLiveData<Task> taskByIdLiveData = new MutableLiveData<>();
    private final MutableLiveData<List<Task>> tasksByStatusLiveData = new MutableLiveData<>();
    private final MutableLiveData<List<Task>> tasksByTypeLiveData = new MutableLiveData<>();
    private final MutableLiveData<List<Task>> tasksByImportanceLiveData = new MutableLiveData<>();
    private final MutableLiveData<List<Task>> tasksByUrgencyLiveData = new MutableLiveData<>();
    
    public MySQLTaskRepository() {
        this.executorService = Executors.newFixedThreadPool(4);
        this.dbHelper = MySQLConnectionHelper.getInstance();
        
        // 初始加载所有任务
        loadAllTasks();
    }
    
    // 加载所有任务
    private void loadAllTasks() {
        executorService.execute(() -> {
            Connection conn = null;
            PreparedStatement stmt = null;
            ResultSet rs = null;
            
            try {
                conn = dbHelper.getConnection();
                String sql = "SELECT * FROM tasks ORDER BY created_date DESC";
                stmt = conn.prepareStatement(sql);
                rs = stmt.executeQuery();
                
                List<Task> tasks = new ArrayList<>();
                while (rs.next()) {
                    tasks.add(mapResultSetToTask(rs));
                }
                
                allTasksLiveData.postValue(tasks);
            } catch (SQLException e) {
                Log.e(TAG, "加载任务失败", e);
            } finally {
                closeResources(conn, stmt, rs);
            }
        });
    }
    
    // 将ResultSet映射为Task对象
    private Task mapResultSetToTask(ResultSet rs) throws SQLException {
        Task task = new Task(rs.getString("title"), rs.getString("description"));
        task.setId(rs.getInt("id"));
        task.setStatus(rs.getString("status"));
        task.setImportance(rs.getInt("importance"));
        task.setUrgency(rs.getString("urgency"));
        task.setRepeat(rs.getString("repeat"));
        task.setType(rs.getString("type"));
        
        // 处理日期
        Timestamp dueTimestamp = rs.getTimestamp("due_date");
        if (dueTimestamp != null) {
            task.setDueDate(new Date(dueTimestamp.getTime()));
        }
        
        Timestamp createdTimestamp = rs.getTimestamp("created_date");
        if (createdTimestamp != null) {
            task.setCreatedDate(new Date(createdTimestamp.getTime()));
        }
        
        task.setCompleted(rs.getBoolean("is_completed"));
        return task;
    }
    
    // 关闭数据库资源
    private void closeResources(Connection conn, PreparedStatement stmt, ResultSet rs) {
        try {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            Log.e(TAG, "关闭资源失败", e);
        }
    }
    
    @Override
    public LiveData<List<Task>> getAllTasks() {
        loadAllTasks();
        return allTasksLiveData;
    }
    
    @Override
    public LiveData<Task> getTaskById(int id) {
        executorService.execute(() -> {
            Connection conn = null;
            PreparedStatement stmt = null;
            ResultSet rs = null;
            
            try {
                conn = dbHelper.getConnection();
                String sql = "SELECT * FROM tasks WHERE id = ?";
                stmt = conn.prepareStatement(sql);
                stmt.setInt(1, id);
                rs = stmt.executeQuery();
                
                if (rs.next()) {
                    Task task = mapResultSetToTask(rs);
                    taskByIdLiveData.postValue(task);
                } else {
                    taskByIdLiveData.postValue(null);
                }
            } catch (SQLException e) {
                Log.e(TAG, "根据ID加载任务失败", e);
            } finally {
                closeResources(conn, stmt, rs);
            }
        });
        
        return taskByIdLiveData;
    }
    
    @Override
    public LiveData<List<Task>> getTasksByStatus(String status) {
        executorService.execute(() -> {
            Connection conn = null;
            PreparedStatement stmt = null;
            ResultSet rs = null;
            
            try {
                conn = dbHelper.getConnection();
                String sql = "SELECT * FROM tasks WHERE status = ? ORDER BY created_date DESC";
                stmt = conn.prepareStatement(sql);
                stmt.setString(1, status);
                rs = stmt.executeQuery();
                
                List<Task> tasks = new ArrayList<>();
                while (rs.next()) {
                    tasks.add(mapResultSetToTask(rs));
                }
                
                tasksByStatusLiveData.postValue(tasks);
            } catch (SQLException e) {
                Log.e(TAG, "根据状态加载任务失败", e);
            } finally {
                closeResources(conn, stmt, rs);
            }
        });
        
        return tasksByStatusLiveData;
    }
    
    @Override
    public LiveData<List<Task>> getTasksByType(String type) {
        executorService.execute(() -> {
            Connection conn = null;
            PreparedStatement stmt = null;
            ResultSet rs = null;
            
            try {
                conn = dbHelper.getConnection();
                String sql = "SELECT * FROM tasks WHERE type = ? ORDER BY created_date DESC";
                stmt = conn.prepareStatement(sql);
                stmt.setString(1, type);
                rs = stmt.executeQuery();
                
                List<Task> tasks = new ArrayList<>();
                while (rs.next()) {
                    tasks.add(mapResultSetToTask(rs));
                }
                
                tasksByTypeLiveData.postValue(tasks);
            } catch (SQLException e) {
                Log.e(TAG, "根据类型加载任务失败", e);
            } finally {
                closeResources(conn, stmt, rs);
            }
        });
        
        return tasksByTypeLiveData;
    }
    
    @Override
    public LiveData<List<Task>> getTasksByImportance(int importance) {
        executorService.execute(() -> {
            Connection conn = null;
            PreparedStatement stmt = null;
            ResultSet rs = null;
            
            try {
                conn = dbHelper.getConnection();
                String sql = "SELECT * FROM tasks WHERE importance = ? ORDER BY created_date DESC";
                stmt = conn.prepareStatement(sql);
                stmt.setInt(1, importance);
                rs = stmt.executeQuery();
                
                List<Task> tasks = new ArrayList<>();
                while (rs.next()) {
                    tasks.add(mapResultSetToTask(rs));
                }
                
                tasksByImportanceLiveData.postValue(tasks);
            } catch (SQLException e) {
                Log.e(TAG, "根据重要程度加载任务失败", e);
            } finally {
                closeResources(conn, stmt, rs);
            }
        });
        
        return tasksByImportanceLiveData;
    }
    
    @Override
    public LiveData<List<Task>> getTasksByUrgency(String urgency) {
        executorService.execute(() -> {
            Connection conn = null;
            PreparedStatement stmt = null;
            ResultSet rs = null;
            
            try {
                conn = dbHelper.getConnection();
                String sql = "SELECT * FROM tasks WHERE urgency = ? ORDER BY created_date DESC";
                stmt = conn.prepareStatement(sql);
                stmt.setString(1, urgency);
                rs = stmt.executeQuery();
                
                List<Task> tasks = new ArrayList<>();
                while (rs.next()) {
                    tasks.add(mapResultSetToTask(rs));
                }
                
                tasksByUrgencyLiveData.postValue(tasks);
            } catch (SQLException e) {
                Log.e(TAG, "根据紧急程度加载任务失败", e);
            } finally {
                closeResources(conn, stmt, rs);
            }
        });
        
        return tasksByUrgencyLiveData;
    }
    
    @Override
    public void insertTask(Task task) {
        executorService.execute(() -> {
            Connection conn = null;
            PreparedStatement stmt = null;
            ResultSet rs = null;
            
            try {
                conn = dbHelper.getConnection();
                String sql = "INSERT INTO tasks (title, description, status, importance, urgency, repeat, type, due_date, created_date, is_completed) " +
                             "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                
                stmt = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
                stmt.setString(1, task.getTitle());
                stmt.setString(2, task.getDescription());
                stmt.setString(3, task.getStatus());
                stmt.setInt(4, task.getImportance());
                stmt.setString(5, task.getUrgency());
                stmt.setString(6, task.getRepeat());
                stmt.setString(7, task.getType());
                
                // 处理日期
                if (task.getDueDate() != null) {
                    stmt.setTimestamp(8, new Timestamp(task.getDueDate().getTime()));
                } else {
                    stmt.setNull(8, java.sql.Types.TIMESTAMP);
                }
                
                if (task.getCreatedDate() != null) {
                    stmt.setTimestamp(9, new Timestamp(task.getCreatedDate().getTime()));
                } else {
                    stmt.setTimestamp(9, new Timestamp(System.currentTimeMillis()));
                }
                
                stmt.setBoolean(10, task.isCompleted());
                
                int affectedRows = stmt.executeUpdate();
                
                if (affectedRows > 0) {
                    // 获取生成的ID
                    rs = stmt.getGeneratedKeys();
                    if (rs.next()) {
                        task.setId(rs.getInt(1));
                    }
                    
                    // 刷新任务列表
                    loadAllTasks();
                }
            } catch (SQLException e) {
                Log.e(TAG, "插入任务失败", e);
            } finally {
                closeResources(conn, stmt, rs);
            }
        });
    }
    
    @Override
    public void updateTask(Task task) {
        executorService.execute(() -> {
            Connection conn = null;
            PreparedStatement stmt = null;
            
            try {
                conn = dbHelper.getConnection();
                String sql = "UPDATE tasks SET title = ?, description = ?, status = ?, importance = ?, " +
                             "urgency = ?, repeat = ?, type = ?, due_date = ?, is_completed = ? " +
                             "WHERE id = ?";
                
                stmt = conn.prepareStatement(sql);
                stmt.setString(1, task.getTitle());
                stmt.setString(2, task.getDescription());
                stmt.setString(3, task.getStatus());
                stmt.setInt(4, task.getImportance());
                stmt.setString(5, task.getUrgency());
                stmt.setString(6, task.getRepeat());
                stmt.setString(7, task.getType());
                
                // 处理日期
                if (task.getDueDate() != null) {
                    stmt.setTimestamp(8, new Timestamp(task.getDueDate().getTime()));
                } else {
                    stmt.setNull(8, java.sql.Types.TIMESTAMP);
                }
                
                stmt.setBoolean(9, task.isCompleted());
                stmt.setInt(10, task.getId());
                
                int affectedRows = stmt.executeUpdate();
                
                if (affectedRows > 0) {
                    // 刷新任务列表
                    loadAllTasks();
                    
                    // 如果有正在观察的任务ID，刷新该任务
                    if (taskByIdLiveData.getValue() != null && taskByIdLiveData.getValue().getId() == task.getId()) {
                        taskByIdLiveData.postValue(task);
                    }
                }
            } catch (SQLException e) {
                Log.e(TAG, "更新任务失败", e);
            } finally {
                closeResources(conn, stmt, null);
            }
        });
    }
    
    @Override
    public void deleteTask(Task task) {
        if (task != null) {
            deleteTaskById(task.getId());
        }
    }
    
    @Override
    public void deleteTaskById(int id) {
        executorService.execute(() -> {
            Connection conn = null;
            PreparedStatement stmt = null;
            
            try {
                conn = dbHelper.getConnection();
                String sql = "DELETE FROM tasks WHERE id = ?";
                
                stmt = conn.prepareStatement(sql);
                stmt.setInt(1, id);
                
                int affectedRows = stmt.executeUpdate();
                
                if (affectedRows > 0) {
                    // 刷新任务列表
                    loadAllTasks();
                    
                    // 如果有正在观察的任务ID，将其置为null
                    if (taskByIdLiveData.getValue() != null && taskByIdLiveData.getValue().getId() == id) {
                        taskByIdLiveData.postValue(null);
                    }
                }
            } catch (SQLException e) {
                Log.e(TAG, "删除任务失败", e);
            } finally {
                closeResources(conn, stmt, null);
            }
        });
    }
    
    // 根据标签获取任务（暂时返回所有任务，因为Task模型中没有tag字段）
    @Override
    public LiveData<List<Task>> getTasksByTag(String tag) {
        // TODO: 实现标签功能
        return getAllTasks(); // 临时返回所有任务
    }
    
    // 根据优先级获取任务（使用urgency字段）
    @Override
    public LiveData<List<Task>> getTasksByPriority(String priority) {
        return getTasksByUrgency(priority);
    }
    
    @Override
    public List<Task> getCompletedTasksByDateSync(java.util.Date date) {
        // 暂时返回空列表，MySQL实现待完善
        return new ArrayList<>();
    }
    
    @Override
    public LiveData<List<Task>> getCompletedTasksByDate(String dateStr) {
        // 暂时返回空的LiveData，MySQL实现待完善
        return new MutableLiveData<>(new ArrayList<>());
    }
} 